<?PHP
#
#   File:  EventFactory.php
#
#   Part of the Metavus digital collections platform
#   Copyright 2016-2021 Edward Almasy and Internet Scout Research Group
#   http://metavus.net
#
# @scout:phpstan

namespace Metavus\Plugins\CalendarEvents;

use Metavus\SearchEngine;
use Metavus\RecordFactory;
use ScoutLib\SearchParameterSet;

class EventFactory extends RecordFactory
{
    /**
     * Class constructor.
     */
    public function __construct()
    {
        $CalPlugin = $GLOBALS["G_PluginManager"]->getPlugin("CalendarEvents");
        parent::__construct($CalPlugin->getSchemaId());
    }

    /**
     * Get the first month that contains an event.
     * @param bool $ReleasedOnly Set to FALSE to consider all events.
     * @return string|null Returns the month and year of the first month that
     *      contains an event, or NULL if there is no first month set.
     */
    public function getFirstMonth(bool $ReleasedOnly = true)
    {
        $StartDateName = $this->Schema->getField("Start Date")->dBFieldName();
        $ReleaseFlagName = $this->Schema->getField("Release Flag")->dBFieldName();

        # construct the base query for the first month that contains an event
        $Query = "SELECT MIN(".$StartDateName.") AS FirstMonth "
            ."FROM Records "
            ."WHERE RecordId >= 0 "
            ."AND SchemaId = ".$this->SchemaId." "
            ."AND ".$StartDateName." != 0";

        # add the release flag constraint if necessary
        if ($ReleasedOnly) {
            $Query .= " AND `".$ReleaseFlagName."` = '1' ";
        }

        # execute the query
        $FirstMonth = $this->DB->query($Query, "FirstMonth");

        # convert the first month to its timestamp
        $Timestamp = strtotime($FirstMonth);

        # return NULL if there is no first month
        if ($Timestamp === false) {
            return null;
        }

        # normalize the month and return
        return date("F Y", $Timestamp);
    }

    /**
     * Get the last month that contains an event.
     * @param bool $ReleasedOnly Set to FALSE to consider all events.
     * @return string|null Returns the month and year of the last month that
     *      contains an event, or NULL if there is no last month set.
     */
    public function getLastMonth(bool $ReleasedOnly = true)
    {
        $EndDateName = $this->Schema->getField("End Date")->dBFieldName();
        $ReleaseFlagName = $this->Schema->getField("Release Flag")->dBFieldName();

        # constrcut the base query for the last month that contains an event
        $Query = "SELECT MAX(".$EndDateName.") AS LastMonth "
            ."FROM Records "
            ."WHERE RecordId >= 0 "
            ."AND SchemaId = ".$this->SchemaId;

        # add the release flag constraint if necessary
        if ($ReleasedOnly) {
            $Query .= " AND `".$ReleaseFlagName."` = '1' ";
        }

        # execute the query
        $LastMonth = $this->DB->query($Query, "LastMonth");

        # convert the last month to its timestamp
        $Timestamp = strtotime($LastMonth);

        # return NULL if there is no last month
        if ($Timestamp === false) {
            return null;
        }

        # normalize the month and return
        return date("F Y", $Timestamp);
    }

    /**
     * Get the event IDs for all events in the given month.
     * @param string $Month Month and year of the events to get.
     * @param bool $ReleasedOnly Set to TRUE to only get released events. This
     *      parameter is optional and defaults to TRUE.
     * @return array Returns an array of event IDs.
     */
    public function getEventIdsForMonth(string $Month, bool $ReleasedOnly = true): array
    {
        # get the database field names for the date fields
        $StartDateName = $this->Schema->getField("Start Date")->dBFieldName();
        $EndDateName = $this->Schema->getField("End Date")->dBFieldName();

        # get the month range
        $Date = (int)strtotime($Month);
        $SafeMonthStart = addslashes(date("Y-m-01 00:00:00", $Date));
        $SafeMonthEnd = addslashes(date("Y-m-t 23:59:59", $Date));

        # leaving this here until query testing with more data can be done
        $Condition = " ((".$StartDateName." >= '".$SafeMonthStart."' ";
        $Condition .= " AND ".$StartDateName." <= '".$SafeMonthEnd."') ";
        $Condition .= " OR (".$EndDateName." >= '".$SafeMonthStart."' ";
        $Condition .= " AND ".$EndDateName." <= '".$SafeMonthEnd."') ";
        $Condition .= " OR (".$StartDateName." < '".$SafeMonthStart."' ";
        $Condition .= " AND ".$EndDateName." >= '".$SafeMonthStart."')) ";

        # retrieve event IDs and return
        return $this->getEventIds($Condition, $ReleasedOnly);
    }


    /**
     * Get the event IDs for upcoming events.
     * @param bool $ReleasedOnly Set to TRUE to get only events with the
     *      "Release Flag" field set to TRUE.  (OPTIONAL, defaults to TRUE)
     * @param int $Limit Number of events to return.  (OPTIONAL, defaults
     *      to all events)
     * @return array Event IDs, sorted by increasing order of start date,
     *      end date, and then title.
     */
    public function getEventIdsForUpcomingEvents(
        bool $ReleasedOnly = true,
        int $Limit = null
    ): array {
        # get the database field names for the date fields
        $StartDateName = $this->Schema->getField("Start Date")->dBFieldName();
        $AllDayName = $this->Schema->getField("All Day")->dBFieldName();

        # get the month range
        $SafeMonthStart = addslashes(date("Y-m-d 00:00:00"));
        $SafeMonthStartWithTime = addslashes(date("Y-m-d H:i:s"));

        # all day and is from today until the end of the month or not all day
        # and is from right now (including time) until the end of the month
        $Condition = " ((".$AllDayName." = 1
                         AND ".$StartDateName." >= '".$SafeMonthStart."')
                       OR ((`".$AllDayName."` = 0 OR `".$AllDayName."` IS NULL)
                         AND ".$StartDateName." >= '".$SafeMonthStartWithTime."'))";

        # retrieve event IDs and return
        return $this->getEventIds($Condition, $ReleasedOnly, $Limit);
    }

    /**
     * Get upcoming events.
     * @param bool $ReleasedOnly Set to TRUE to get only events with the
     *      "Release Flag" field set to TRUE.  (OPTIONAL, defaults to TRUE)
     * @param int $Limit Number of events to return.  (OPTIONAL, defaults
     *      to all events)
     * @return array Event, sorted by increasing order of start date, end
     *      date, and then title, and indexed by event ID.
     */
    public function getUpcomingEvents(
        bool $ReleasedOnly = true,
        int $Limit = null
    ): array {
        $Ids = $this->getEventIdsForUpcomingEvents($ReleasedOnly, $Limit);
        $Events = [];
        foreach ($Ids as $Id) {
            $Events[$Id] = new Event($Id);
        }
        return $Events;
    }

    /**
     * Get counts for events for the future, occurring, and past events.
     * @param bool $ReleasedOnly Set to FALSE to get the counts for all events.
     * @return array Returns the counts for future, occurring, and past events.
     */
    public function getEventCountsByTense(bool $ReleasedOnly = true): array
    {
        # get the database field names for the date fields
        $StartDateName = $this->Schema->getField("Start Date")->dBFieldName();
        $EndDateName = $this->Schema->getField("End Date")->dBFieldName();
        $AllDayName = $this->Schema->getField("All Day")->dBFieldName();
        $ReleaseFlagName = $this->Schema->getField("Release Flag")->dBFieldName();

        # get the month range
        $SafeTodayStart = addslashes(date("Y-m-d 00:00:00"));
        $SafeTodayWithTime = addslashes(date("Y-m-d H:i:s"));
        $SafeTodayEnd = addslashes(date("Y-m-d 23:59:59"));

        # construct the first part of the query
        $PastEventsCount = $this->DB->queryValue(
            "
            SELECT COUNT(*) as EventCount FROM Records
            WHERE `RecordId` >= 0
            AND `SchemaId` = ".$this->SchemaId
            ." ".($ReleasedOnly ? "AND `".$ReleaseFlagName."` = 1" : "")."
            AND ((`".$AllDayName."` = 1
                   AND `".$EndDateName."` < '".$SafeTodayStart."')
                 OR ((`".$AllDayName."` = 0 OR `".$AllDayName."` IS NULL)
                   AND `".$EndDateName."` < '".$SafeTodayWithTime."'))",
            "EventCount"
        );

        # rather than doing complex SQL query logic, just get the count of all
        # of the events and subtract the others below
        $AllEventsCount = $this->DB->queryValue(
            "
            SELECT COUNT(*) as EventCount FROM Records
            WHERE `RecordId` >= 0
            AND `SchemaId` = '".addslashes($this->SchemaId)."'
            ".($ReleasedOnly ? "AND `".$ReleaseFlagName."` = 1" : ""),
            "EventCount"
        );

        $FutureEventsCount = $this->DB->queryValue(
            "
            SELECT COUNT(*) as EventCount FROM Records
            WHERE `RecordId` >= 0
            AND `SchemaId` = '".addslashes($this->SchemaId)."'
            ".($ReleasedOnly ? "AND `".$ReleaseFlagName."` = 1" : "")."
            AND ((`".$AllDayName."` = 1
                   AND `".$StartDateName."` > '".$SafeTodayEnd."')
                 OR ((`".$AllDayName."` = 0 OR `".$AllDayName."` IS NULL)
                   AND `".$StartDateName."` > '".$SafeTodayWithTime."'))",
            "EventCount"
        );

        # return the counts
        return [
            "Past" => $PastEventsCount,
            "Occurring" => $AllEventsCount - $PastEventsCount - $FutureEventsCount,
            "Future" => $FutureEventsCount
        ];
    }

    /**
     * Get counts for events for each month.
     * @param bool $ReleasedOnly Set to FALSE to get the counts for all events.
     * @return array Returns the event counts for each month.
     */
    public function getEventCounts(bool $ReleasedOnly = true): array
    {
        # get the bounds of the months
        $FirstMonth = $this->getFirstMonth();
        $LastMonth = $this->getLastMonth();

        # convert the month strings to timestamps
        $FirstMonthTimestamp = strtotime($FirstMonth);
        $LastMonthTimestamp = strtotime($LastMonth);

        # print an emprty array if the timestamps aren't valid or there are no
        # events
        if ($FirstMonthTimestamp === false || $LastMonthTimestamp === false) {
            return [];
        }

        # get the boundaries as numbers
        $FirstYearNumber = intval(date("Y", $FirstMonthTimestamp));
        $FirstMonthNumber = intval(date("m", $FirstMonthTimestamp));
        $LastYearNumber = intval(date("Y", $LastMonthTimestamp));
        $LastMonthNumber = intval(date("m", $LastMonthTimestamp));

        # start off the query
        $Query = "SELECT ";

        # get the database field names for the date fields
        $StartDateName = $this->Schema->getField("Start Date")->dBFieldName();
        $EndDateName = $this->Schema->getField("End Date")->dBFieldName();
        $ReleaseFlagName = $this->Schema->getField("Release Flag")->dBFieldName();

        # loop through the years
        for ($i = $FirstYearNumber; $i <= $LastYearNumber; $i++) {
            # loop through the months
            for ($j = ($i == $FirstYearNumber ? $FirstMonthNumber : 1); #
                 ($i == $LastYearNumber ? $j <= $LastMonthNumber : $j < 13); #
                 $j++) {
                $ColumnName = date("MY", (int)mktime(0, 0, 0, $j, 1, $i));
                $LastDay = date("t", (int)mktime(0, 0, 0, $j, 1, $i));

                $Start = $i."-".$j."-01 00:00:00";
                $End = $i."-".$j."-".$LastDay." 23:59:59";

                $Query .= " sum((".$StartDateName." >= '".$Start."' ";
                $Query .= " AND ".$StartDateName." <= '".$End."') ";
                $Query .= " OR (".$EndDateName." >= '".$Start."' ";
                $Query .= " AND ".$EndDateName." <= '".$End."') ";
                $Query .= " OR (".$StartDateName." < '".$Start."' ";
                $Query .= " AND ".$EndDateName." >= '".$Start."')) AS ".$ColumnName.", ";
            }
        }

        # remove the trailing comma
        $Query = rtrim($Query, ", ") . " ";

        # add the table name
        $Query .= "FROM Records WHERE RecordId >= 0"
               ." AND SchemaId = ".$this->SchemaId;

        # add the release flag constraint if necessary
        if ($ReleasedOnly) {
            $Query .= " AND `".$ReleaseFlagName."` = '1' ";
        }

        # this may be a very long query and could have very long results
        # avoid caching it
        $PreviousSetting = $this->DB->caching();
        $this->DB->caching(false);
        $this->DB->query($Query);
        $Result = $this->DB->fetchRow();
        $this->DB->caching($PreviousSetting);

        return $Result;
    }

    /**
     * Filter a list of events to either return those with a specified
     * owner or those that have no owner.
     * @param array $EventIds List of event IDs to filter.
     * @param int|null $OwnerId Desired owner ID or NULL for events with no owner.
     * @return array Filtered list of event IDs.
     */
    public function filterEventsByOwner(array $EventIds, $OwnerId): array
    {
        $OwnerFieldId = $this->Schema->getField("Owner")->id();

        if ($OwnerId === null) {
            # get the list of all events that have an owner
            $this->DB->query(
                "SELECT RecordId FROM RecordUserInts"
                ." WHERE FieldId = ".$OwnerFieldId
            );
            $OwnedEventIds = $this->DB->fetchColumn("RecordId");

            # and remove those events from the list we were given
            $Result = array_diff($EventIds, $OwnedEventIds);
        } else {
            $OwnedEventIds = $this->getIdsOfMatchingRecords(
                [$OwnerFieldId => $OwnerId]
            );

            # and filter out any events that weren't on that list
            $Result = array_intersect($EventIds, $OwnedEventIds);
        }

        return $Result;
    }

    /**
     * Filter a list of events based on search parameters.
     * @param array $EventIds List of event IDs to filter.
     * @param SearchParameterSet $SearchParams Search parameters to use when filtering.
     * @return array Filtered list of event IDs.
     */
    public function filterEventsBySearchParameters(
        array $EventIds,
        SearchParameterSet $SearchParams
    ): array {
        $SEngine = new SearchEngine();
        $SearchParams->itemTypes($this->SchemaId);
        $SearchResults = $SEngine->search($SearchParams);
        $SearchResultEventIds = array_keys($SearchResults);
        return array_intersect($EventIds, $SearchResultEventIds);
    }

    /**
     * Fetch event IDs using supplied SQL condition.
     * @param string $Condition Additional SQL condition string.
     * @param bool $ReleasedOnly Set to TRUE to get only events with the
     *      "Release Flag" field set to TRUE.  (OPTIONAL, defaults to TRUE)
     * @param int $Limit Number of events to return.  (OPTIONAL, defaults
     *      to all events)
     * @return array Matched event IDs, sorted by increasing order of start date,
     *      end date, and then title.
     */
    protected function getEventIds(
        string $Condition,
        bool $ReleasedOnly = true,
        int $Limit = null
    ): array {
        $TitleName = $this->Schema->getField("Title")->dBFieldName();
        $StartDateName = $this->Schema->getField("Start Date")->dBFieldName();
        $EndDateName = $this->Schema->getField("End Date")->dBFieldName();

        # construct the first part of the query
        $Query = "SELECT RecordId FROM Records "
                ."WHERE RecordId >= 0 AND SchemaId = ".$this->SchemaId;

        # if only released events should be returned
        if ($ReleasedOnly) {
            $ReleaseFlagName = $this->Schema->getField(
                "Release Flag"
            )->dBFieldName();
            $Query .= " AND ".$ReleaseFlagName." = '1' ";
        }

        # add the condition string
        $Query .= " AND " . $Condition;

        # add sorting parameters
        $Query .= " ORDER BY ".$StartDateName." ASC, ";
        $Query .= " ".$EndDateName." ASC, ";
        $Query .= " ".$TitleName." ASC ";

        # add the limit if given
        if (!is_null($Limit)) {
            $Query .= " LIMIT " . intval($Limit). " ";
        }

        # execute the query
        $this->DB->query($Query);

        # return the IDs
        return $this->DB->fetchColumn("RecordId");
    }
}
